#!/bin/bash       
################################################################################
## Monolith Toolkit for MySQL
## NAME: mt-connections-log.sh
## DATE: 2008-10-01
## AUTHOR: Matt Reid
## WEBSITE: http://monolith-mysql.com && http://themattreid.com 
## EMAIL: themattreid@gmail.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
################################################################################
## Copyright 2008 Matt Reid
## All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
# 1. Redistributions of source code must retain the above copyright
#    notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions and the following disclaimer in the
#    documentation and/or other materials provided with the distribution.
# 3. The name of the author may not be used to endorse or promote products
#    derived from this software without specific prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
# IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
# OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
# IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
# NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
# DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
# THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
# THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
##################################################################################
#
## WHAT THIS SCRIPT DOES #########################################################
#Logs MySQL connections - run out of crontab for periodic trending
#
#Crontab setting if you need it
# #mysql connection logging
# */5 * * * * root /usr/local/bin/mysql_connections_log
####
##SET USER OPTIONS HERE
USER="root"
PASS="pass"
HOST="localhost"
THRESHOLD="50" # over this number of connections - get debug information (user/query/status/etc)
LOGDIR=/var/log/mysql
##END OF USER SETTINGS
##################################################################################
LOGDATE=`date +"%c"`
DATE=`date +"%F_%H-%M-%S"`

function run_conn() {
    FILE=`echo 'show processlist;' | mysql -u ${USER} --password=${PASS}`
    COUNT=`echo 'show processlist;' | mysql -u ${USER} --password=${PASS}| wc -l`
    echo `date` - $COUNT >> $LOGDIR/mysqlcount.log
    mkdir -p $LOGDIR/FULL/$DATE
    echo "$FILE" > $LOGDIR/FULL/$DATE/$DATE.log

    if [ "$COUNT" -ge ${THRESHOLD} ]; then
        echo `date` >> $LOGDIR/debug.log
        echo " " >> $LOGDIR/debug.log
        echo 'show processlist;' | mysql -u root --password=${PASS} >> $LOGDIR/debug.log
        echo " " >> $LOGDIR/debug.log
    fi
}

function check_server {
    if [ "$HOST" = "localhost" ] || [ "$HOST" = "127.0.0.1" ]; then
        pgrep mysql
        if test $? -eq 0 ; then
            run_conn
        else
            echo "${LOGDATE} - MySQL Connection Logging: Database not running on this node and localhost/127.0.0.1 was specified as HOST. Aborting check." >> /var/log/messages
        fi
    else
        run_conn
    fi
}

test -d ${LOGDIR}
if [ $? = "0" ]; then
    check_server
else 
    echo "${LOGDATE} - MySQL Connection Logging: ${LOGDIR} does not exist. Creating." >> /var/log/messages
    check_server
fi